"""
Case Type   : 功能测试 -- gsql执行统计时间测试
Case Name   : 执行定义角色的DCL语句,查看db_tim和子时间的误差在10%以内
Create At   : 2023-12-11
Owner       : kyeleze
Description :
    1. 执行定义角色的DCL语句
    2. 查看每个sql的db_tim和子时间的误差
Expect      :
    1. 执行成功
    2. 误差在10%以内
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

PrimarySh = CommonSH('PrimaryDbUser')


@unittest.skipIf(1 == PrimarySh.get_node_num(), '非主备环境不执行')
class DBTimeCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.role_name = 'role_dbtime_test_045'
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'
        step = '----清理统计信息----'
        self.log.info(step)
        clean_sql = "select * from reset_unique_sql('global', 'all', 0);"
        result = self.primary_sh.execut_db_sql(clean_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn('t', result.strip().splitlines()[2], '执行失败' + step)

    def test_sql_dbtime(self):
        step = '----step1:执行定义角色的DCL语句 expect:执行成功----'
        self.log.info(step)
        ddl_sql = f"drop role if exists {self.role_name};" \
                  f"create role {self.role_name} with login " \
                  f"password 'huawi@123' valid begin '2023-01-01' valid until" \
                  f" '2030-01-01';" \
                  f"alter role {self.role_name} identified by " \
                  f"'Huawi@1234' replace 'testui@123';" \
                  f"alter role {self.role_name} sysadmin;" \
                  f"drop role {self.role_name};"
        result = self.primary_sh.execut_db_sql(ddl_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.assertEqual(2, result.count(self.constant.ALTER_ROLE_SUCCESS_MSG),
                         '执行失败' + step)
        self.assertEqual(2, result.count(self.constant.DROP_ROLE_SUCCESS_MSG),
                         '执行失败' + step)

        step = '--step2:查看每个sql的db_tim和子时间的误差 expect:误差在10%以内--'
        self.log.info(step)
        sql_list = [f'create role {self.role_name} with',
                    f'alter role {self.role_name} identified',
                    f'alter role {self.role_name} sysadmin',
                    f'drop role {self.role_name}']
        for sql in sql_list:
            check_sql = f"select (select round((db_time-total_time)/db_time," \
                        f"2) from (select db_time, sum(execution_time + " \
                        f"parse_time + plan_time + rewrite_time + " \
                        f"pl_execution_time + pl_compilation_time + " \
                        f"data_io_time + net_send_time + srt1_q + " \
                        f"srt2_simple_query + srt3_analyze_rewrite + " \
                        f"srt4_plan_query + srt5_light_query + srt6_p + " \
                        f"srt7_b + srt8_e + srt9_d + srt10_s + srt11_c + " \
                        f"srt12_u + srt13_before_query + srt14_after_query) " \
                        f"as total_time from get_instr_unique_sql() where " \
                        f"query like '{sql}%' group by db_time)) < 0.1 as res;"
            result = self.primary_sh.execut_db_sql(check_sql,
                                                   sql_type=self.user_param)
            self.log.info(result)
            self.assertIn('', result.strip().splitlines()[2],
                          '执行失败' + step)

    def tearDown(self):
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
